--------------------------------------------------------------------------------
-- @name: tracing reads
-- @author: dion cho
-- @note: test case for the way of tracing reads
--------------------------------------------------------------------------------

@capture_on
set echo on
set pages 10000
set lines 200
set timing off
set trimspool on
set serveroutput off

drop table t1 purge;

create table t1(c1 char(1));

select
	tablespace_name,
	initial_extent,
	allocation_type,
	segment_space_management
from dba_tablespaces
where tablespace_name = (
	select tablespace_name from user_tables where table_name = 'T1');

exec dbms_stats.gather_table_stats(user, 't1');

-- to disable the side effect of the hard parse
select /* no_rows */ * from t1;
select /* 1 rows */ * from t1;
select /* many rows */ count(*) from t1;

-- without rows
alter system flush buffer_cache;

set autot on
select * from t1;
set autot off

alter system flush buffer_cache;

exec tpack.begin_diag_trace(userenv('sid'), 10046, 8);

select /* no_rows */ * from t1;

exec tpack.end_diag_trace(userenv('sid'), 10046);


select * from table(tpack.get_diag_trace(userenv('sid')));


col object_name format a10
col file_no format a10
col block_no format a10
col blocks format a10
col class format a20
select 
	(select object_name from dba_objects where data_object_id = obj_no) as object_name,
	file_no,
	block_no,
	blocks,
	(select w.class
		from v$bh b, (select rownum as r, class from v$waitstat) w 
		where b.file# = file_no and b.block# = block_no and b.class#=w.r and rownum = 1) as class
from (
	select 
		substr(txt, 1, instr(txt,'.')-1) as file_no,
		substr(txt, instr(txt,'.')+1, instr(txt,'.',1,2)-instr(txt,'.')-1) as block_no,
		substr(txt, instr(txt,'.',1,2)+1, instr(txt,'.',1,3)-instr(txt,'.',1,2)-1) as blocks,
		substr(txt, instr(txt,'.',1,3)+1) as obj_no
	from (
		select 
		regexp_replace(column_value,
			'WAIT #[[:digit:]]+: nam=''[[:print:]]+'' ela= [[:digit:]]+ file#=([[:digit:]]+) block#=([[:digit:]]+) blocks=([[:digit:]]+) obj#=([[:digit:]]+) tim=[[:digit:]]+',
			'\1.\2.\3.\4') as txt
		from table(tpack.get_history_diag_trace)
		where column_value like '%db file%read%'
	)
);

set serveroutput on
exec show_space('T1');
set serveroutput off

-- with one row
insert into t1 values('1');
commit;

alter system flush buffer_cache;

set autot on
select * from t1;
set autot off

alter system flush buffer_cache;

exec tpack.begin_diag_trace(userenv('sid'), 10046, 8);

select /* 1 rows */ * from t1;

exec tpack.end_diag_trace(userenv('sid'), 10046);

select * from table(tpack.get_diag_trace(userenv('sid')));

col object_name format a10
col file_no format a10
col block_no format a10
col blocks format a10
col class format a20
select 
	(select object_name from dba_objects where data_object_id = obj_no) as object_name,
	file_no,
	block_no,
	blocks,
	(select w.class
		from v$bh b, (select rownum as r, class from v$waitstat) w 
		where b.file# = file_no and b.block# = block_no and b.class#=w.r and rownum = 1) as class
from (
	select 
		substr(txt, 1, instr(txt,'.')-1) as file_no,
		substr(txt, instr(txt,'.')+1, instr(txt,'.',1,2)-instr(txt,'.')-1) as block_no,
		substr(txt, instr(txt,'.',1,2)+1, instr(txt,'.',1,3)-instr(txt,'.',1,2)-1) as blocks,
		substr(txt, instr(txt,'.',1,3)+1) as obj_no
	from (
		select 
		regexp_replace(column_value,
			'WAIT #[[:digit:]]+: nam=''[[:print:]]+'' ela= [[:digit:]]+ file#=([[:digit:]]+) block#=([[:digit:]]+) blocks=([[:digit:]]+) obj#=([[:digit:]]+) tim=[[:digit:]]+',
			'\1.\2.\3.\4') as txt
		from table(tpack.get_history_diag_trace)
		where column_value like '%db file%read%'
	)
);

set serveroutput on
exec show_space('T1');
set serveroutput off



-- with many rows
insert into t1 
select '1' from dual
connect by level <= 100000;
commit;


alter system flush buffer_cache;

set autot on
select /* many rows */ count(*) from t1;
set autot off

alter system flush buffer_cache;

exec tpack.begin_diag_trace(userenv('sid'), 10046, 8);

select /* many rows */ count(*) from t1;

exec tpack.end_diag_trace(userenv('sid'), 10046);

select * from table(tpack.get_diag_trace(userenv('sid')));

col object_name format a10
col file_no format a10
col block_no format a10
col blocks format a10
col class format a20
select 
	(select object_name from dba_objects where data_object_id = obj_no) as object_name,
	file_no,
	block_no,
	blocks,
	(select w.class
		from v$bh b, (select rownum as r, class from v$waitstat) w 
		where b.file# = file_no and b.block# = block_no and b.class#=w.r and rownum = 1) as class
from (
	select 
		substr(txt, 1, instr(txt,'.')-1) as file_no,
		substr(txt, instr(txt,'.')+1, instr(txt,'.',1,2)-instr(txt,'.')-1) as block_no,
		substr(txt, instr(txt,'.',1,2)+1, instr(txt,'.',1,3)-instr(txt,'.',1,2)-1) as blocks,
		substr(txt, instr(txt,'.',1,3)+1) as obj_no
	from (
		select 
		regexp_replace(column_value,
			'WAIT #[[:digit:]]+: nam=''[[:print:]]+'' ela= [[:digit:]]+ file#=([[:digit:]]+) block#=([[:digit:]]+) blocks=([[:digit:]]+) obj#=([[:digit:]]+) tim=[[:digit:]]+',
			'\1.\2.\3.\4') as txt
		from table(tpack.get_history_diag_trace)
		where column_value like '%db file%read%'
	)
);

set serveroutput on
exec show_space('T1');
set serveroutput off



@capture_off
